import os
import time
import re
import pymysql
from collections import defaultdict
from .pre_config_brower import request_fun
from xlsxwriter import Workbook
from PIL import Image


class SaveFile:
    def __init__(self, excel_file_full_path_name, save_img_dir_path):
        self.excel_file_full_path_name = excel_file_full_path_name
        self.save_img_dir_path = save_img_dir_path

    def download_img(self, pic_url, pic_name=None):
        if not pic_name:
            pic_name = pic_url.strip().split('/')[-1]
            pic_name = pic_name.split('?')[0]
        else:
            # pic_name = pic_name.strip().replace("\"", "").replace("\'", "")
            # pic_name = pic_name.strip().replace(",", " ").replace(" ", "_").replace("/", "_")
            pic_name = re.sub('([\"\',/ ])', "_", pic_name)

        if pic_name[-4] != ".":
            pic_name += ".jpg"

        img_full_path_name = os.path.join(self.save_img_dir_path, pic_name)
        if pic_name in os.listdir(self.save_img_dir_path):
            pass
        else:
            try:
                img = request_fun(pic_url.strip())
                with open(img_full_path_name, 'wb') as p:
                    p.write(img)
                with Image.open(img_full_path_name) as jpg:
                    if jpg.format == "WEBP":
                        jpg.save(img_full_path_name, "JPEG")
            except:
                print("download_img:\n", now_time(),
                      "download PIC failure: %s" % pic_url)
                failure_img = os.path.join(os.getcwd(), "downloadfailure.jpg")
                # os.system('cp  %s  %s' % (failure_img, img_full_path_name))
        return img_full_path_name

    def create_excel(self):
        # create excel file
        new_f = Workbook(self.excel_file_full_path_name)
        sheet1 = new_f.add_worksheet("amazon")
        sheet1_format = new_f.add_format()
        sheet1_format.set_text_wrap()

        sheet1.set_column(0, 0, 28)
        sheet1.set_column(1, 4, 60)
        sheet1.set_column(2, 2, 15)
        # set headers
        sheet1.set_row(0, 18)
        headers = ['IMG', 'PRODUCE NAME', 'PRODUCE PRICE',
                   'LINK', 'STARS', "REVIEWS", "RANK", "SALES"]
        for seq, header in enumerate(headers):
            sheet1.write(0, seq, header)

        return sheet1, sheet1_format, new_f


class MySQL:
    def __init__(self, **con_info):
        mysql_info = defaultdict()
        mysql_info['host'] = con_info.get('host', '127.0.0.1')
        mysql_info['port'] = con_info.get('port', 3306)
        mysql_info['charset'] = con_info.get('charset', 'utf8')
        mysql_info['user'] = con_info['username']
        mysql_info['db'] = con_info['db_name']
        if 'passwd' in con_info:
            mysql_info['passwd'] = con_info['passwd']

        self.conn = pymysql.connect(
            **mysql_info, cursorclass=pymysql.cursors.DictCursor)
        self.cursor = self.conn.cursor()

    def db_insert(self, items, info="item"):
        if info == "item":
            insert_cmd = "replace into productList(companyName, category, category2, productName, " \
                "productUrl, productPrice, productPicUrl, productPicPath"\
                ", productMore) VALUES (%s,%s,%s, %s, %s,%s,%s,%s, %s)"
            data = (items["companyName"], items["category"], items["category2"], items["productName"],
                    items["productUrl"], items["productPrice"], items["productPicUrl"],
                    items["productPicPath"], str(items["productMore"]))
        elif info == "product":
            insert_cmd = "replace into productCompany(companyName, category, category2, " \
                "url) VALUES (%s,%s,%s,%s)"
            data = (items["companyName"], items["category"],
                    items["category2"], items["url"])
        else:
            print("not supported !!")
            assert False

        try:
            self.cursor.execute(insert_cmd, data)
            self.conn.commit()
        except Exception as e:
            print(e)
            print(items)
            self.conn.rollback()

    def db_query(self, requestInfo, tableName="productList", where_str=None):
        query_cmd = "select %s from %s where %s" % (
            requestInfo, tableName, where_str)
        try:
            self.cursor.execute(query_cmd)
            return self.cursor.fetchall()

        except Exception as e:
            print(e)
            print(requestInfo, tableName, where_str)
            self.conn.rollback()

    def db_close(self):
        self.conn.close()


def now_time():
    current_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
    return current_time + ': '


if __name__ == "__main__":
    con_sql_info = {'host': '49.235.84.115',
                    'port': 6606,
                    'username': 'root',
                    'passwd': 'bxf198592',
                    'db_name': 'jetSpider'}
    SQL = MySQL(**con_sql_info)
    SQL.cursor.execute('desc productList;')
    rows = SQL.cursor.fetchall()
    print(rows)
